from flask import Blueprint, render_template, request, send_file, flash, redirect, url_for
from flask_login import login_required
from app.models import MonitorPoint, MonitorData
from datetime import datetime, timedelta
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill
import io

bp = Blueprint('history', __name__, url_prefix='/history')

@bp.route('/')
@login_required
def index():
    points = MonitorPoint.query.all()
    point_id = request.args.get('point_id', type=int)
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    
    if start_date:
        start_date = datetime.strptime(start_date, '%Y-%m-%d')
    else:
        start_date = datetime.utcnow() - timedelta(days=7)
    
    if end_date:
        end_date = datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1)
    else:
        end_date = datetime.utcnow()
    
    if point_id:
        data = MonitorData.query.filter(
            MonitorData.point_id == point_id,
            MonitorData.timestamp >= start_date,
            MonitorData.timestamp <= end_date
        ).order_by(MonitorData.timestamp.desc()).all()
        selected_point = MonitorPoint.query.get(point_id)
    else:
        data = []
        selected_point = None
    
    return render_template('history/index.html',
                         points=points,
                         selected_point=selected_point,
                         data=data,
                         start_date=start_date.strftime('%Y-%m-%d'),
                         end_date=(end_date - timedelta(days=1)).strftime('%Y-%m-%d')) 

@bp.route('/export')
@login_required
def export_data():
    point_id = request.args.get('point_id', type=int)
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    
    if not all([point_id, start_date, end_date]):
        flash('导出参数不完整', 'danger')
        return redirect(url_for('history.index'))
    
    # 获取监控点信息
    point = MonitorPoint.query.get_or_404(point_id)
    
    # 转换日期
    start = datetime.strptime(start_date, '%Y-%m-%d')
    end = datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1)
    
    # 查询数据
    data = MonitorData.query.filter(
        MonitorData.point_id == point_id,
        MonitorData.timestamp >= start,
        MonitorData.timestamp <= end
    ).order_by(MonitorData.timestamp.desc()).all()
    
    # 创建工作簿
    wb = Workbook()
    ws = wb.active
    ws.title = "监测数据"
    
    # 设置标题样式
    title_font = Font(bold=True)
    title_fill = PatternFill(start_color="CCE5FF", end_color="CCE5FF", fill_type="solid")
    title_alignment = Alignment(horizontal='center')
    
    # 写入基本信息
    ws['A1'] = f"监控点: {point.name}"
    ws['A2'] = f"位置: {point.location}"
    ws['A3'] = f"描述: {point.description}"
    ws['A4'] = f"导出时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"
    
    # 写入表头
    headers = ['时间', '溶解氧(mg/L)', 'pH值', '温度(℃)', '氨氮(mg/L)', '浊度(NTU)']
    for col, header in enumerate(headers, 1):
        cell = ws.cell(row=6, column=col)
        cell.value = header
        cell.font = title_font
        cell.fill = title_fill
        cell.alignment = title_alignment
    
    # 写入数据
    for row, item in enumerate(data, 7):
        ws.cell(row=row, column=1, value=item.timestamp.strftime('%Y-%m-%d %H:%M:%S'))
        ws.cell(row=row, column=2, value=round(item.dissolved_oxygen, 2))
        ws.cell(row=row, column=3, value=round(item.ph_value, 2))
        ws.cell(row=row, column=4, value=round(item.temperature, 1))
        ws.cell(row=row, column=5, value=round(item.ammonia_nitrogen, 2))
        ws.cell(row=row, column=6, value=round(item.turbidity, 2))
    
    # 调整列宽
    for column in ws.columns:
        max_length = 0
        column = list(column)
        for cell in column:
            if cell.value:
                max_length = max(max_length, len(str(cell.value)))
        ws.column_dimensions[column[0].column_letter].width = max_length + 2
    
    # 保存到内存中
    excel_file = io.BytesIO()
    wb.save(excel_file)
    excel_file.seek(0)
    
    # 生成文件名
    filename = f"{point.name}_数据_{start_date}_{end_date}.xlsx"
    
    return send_file(
        excel_file,
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        as_attachment=True,
        download_name=filename
    ) 